from flask import (
    Blueprint, flash, g, redirect, render_template, request, url_for
)
from werkzeug.exceptions import abort
from typing import List
import sqlite3

from forumAPP.auth import login_required
from forumAPP.database import get_database_connection

bp = Blueprint('blog', __name__)


@bp.route('/', methods=('GET', 'POST',))
def index():
    # url_for('blog.update', id=article['numberID'])
    print('主页:', request.args)

    page: int = request.args.get('page')
    if page is None:
        page = 1
    else:
        page = int(page)

    # return redirect(url_for('blog.split_page', ID=1))
    conn = get_database_connection()

    SQL_code: str = '''
    SELECT a.numberID, a.authorID, a.last_modified_time,a.title, a.body,
        SUM(l.direction) as num_likes,
        u.nickname, u.gender, u.region, u.career
    FROM article AS a, user AS u
    LEFT OUTER JOIN like_dislike AS l
        ON l.articleID=a.numberID
    WHERE a.authorID=u.numberID
    GROUP BY a.numberID
    ORDER BY a.last_modified_time DESC
    LIMIT 5 OFFSET ?;
    '''

    articles = conn.execute(SQL_code, ((page-1)*5,)).fetchall()

    print('主页,数据库读取----')
    for row in articles:
        print(row['numberID'], row['authorID'], row['title'], row['nickname'])

    SQL_code = '''
    SELECT COUNT(*) AS total
    FROM article;
    '''
    num_article: int = conn.execute(SQL_code).fetchone()['total']
    print('----文章总数:%d----' % num_article)
    num_pages: int
    if num_article % 5 == 0:
        num_pages = int(num_article/5)
    else:
        num_pages = int(num_article/5)+1
    pages = range(1, num_pages+1)

    return render_template('/blog/index.html', articles=articles, pages=pages)


@bp.route('/create', methods=('GET', 'POST',))
@login_required
def create():
    if request.method == 'GET':
        return render_template('/blog/create.html')
    # POST, 用户提交表单
    title: str = request.form.get('title')
    body: str = request.form.get('body')

    conn = get_database_connection()
    SQL_code: str = '''
    INSERT INTO article
            (authorID, title, body)
    VALUES  (?,?,?);
    '''
    conn.execute(SQL_code, (g.user['numberID'], title, body,))
    conn.commit()

    print('创建文章, 用户输入:', title, body)
    return redirect(url_for('blog.index'))


@bp.route('/<int:ID>/detail', methods=('GET', 'POST',))
def detail(ID: int):
    conn = get_database_connection()
    if request.method == 'POST':
        print('提交评论,用户输入:', request.form)
        SQL_code: str = '''
        INSERT INTO comment
                (authorID, articleID, prevCommentID,content)
        VALUES  (?,         ?,          -1,           ?);
        '''
        if g.user is None:
            return "用户未登录，不可编辑评论"
        conn.execute(
            SQL_code, (g.user['numberID'], ID, request.form.get('comment_content'),))
        conn.commit()

    SQL_code: str = '''
    SELECT a.numberID, a.authorID, a.last_modified_time,a.title, a.body,
        u.nickname, u.gender, u.region, u.career
    FROM article AS a
    INNER JOIN user AS u
        ON a.authorID = u.numberID
    WHERE a.numberID =?;
    '''

    article = conn.execute(SQL_code, (ID,)).fetchone()
    if article is None:
        return "<h1>文章不存在</h1>"
    print("文章详情, 数据库读取:", article['numberID'],
          article['title'], article['nickname'])

    SQL_code = '''
    SELECT c.numberID, c.authorID, c.articleID,
        c.last_modified_time,c.content, u.nickname, u.gender, u.region, 
        u.career
    FROM comment AS c
    INNER JOIN user AS u
        ON c.authorID = u.numberID
    WHERE articleID = ? AND prevCommentID <=0
    ORDER BY c.last_modified_time DESC;
    '''
    comment_list = conn.execute(SQL_code, (ID,)).fetchall()
    print('文章详情, 评论区:')
    for row in comment_list:
        print(type(row), row['articleID'], row['content'],
              row['authorID'], row['nickname'])
    if len(comment_list) <= 0:
        comment_list = None

    # 点赞详情
    SQL_code = '''
        SELECT *
        FROM like_dislike
        WHERE articleID=? AND direction==true;
    '''
    all_likes: List[sqlite3.Row] = conn.execute(SQL_code, (ID,)).fetchall()
    num_likes = len(all_likes)
    current_user_like = False
    if g.user is not None:
        for row in all_likes:
            if row['launcherID'] == g.user['numberID']:
                current_user_like = True
                break

    return render_template('/blog/detail.html',
                           num_likes=num_likes,
                           current_user_like=current_user_like,
                           article=article,
                           comment_list=comment_list)


@bp.route('/<int:id>/delete')
@login_required
def delete(id: int):
    SQL_code: str = '''
    DELETE FROM article
    WHERE numberID=? AND authorID=?;
    '''
    conn = get_database_connection()
    conn.execute(SQL_code, (id, g.user['numberID'],))
    conn.commit()
    return redirect(url_for('blog.index'))


@ bp.route('/<int:id>/update', methods=('GET', 'POST',))
@ login_required
def update(id):
    SQL_code: str = '''
    SELECT *
    FROM article
    WHERE numberID=? AND authorID = ?;
    '''
    conn = get_database_connection()
    article = conn.execute(
        SQL_code, (id, g.user['numberID'])).fetchone()

    if article is None:
        return "文章不存在or无访问权限"

    if request.method == 'GET':
        return render_template('/blog/update.html', article=article)
    # POST,用户提交表单
    print('修改文章,内存读取:', request.form)

    SQL_code = '''
    UPDATE article
    SET title=?, body=?, last_modified_time=CURRENT_TIMESTAMP
    WHERE numberID=?;
    '''
    conn.execute(SQL_code, (request.form.get(
        'title'), request.form.get('body'), id,))
    conn.commit()
    return redirect(url_for('blog.index'))
